--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_CPT_FAM AS (SELECT * FROM &erp.CPT_FAM WHERE 1=2);




DELETE FROM se_CPT_FAM;

 INSERT INTO se_CPT_FAM
 (SELECT id fam_cod,
         0 enreg_id,
         'N' enreg_s,
         libelle fam_lib,
         NULL fam_lib2,
         'T' tiers_typ
    FROM uv_cfamcpta) ;

 INSERT INTO se_CPT_FAM
 (SELECT DISTINCT famcpta fam_cod,
         0 enreg_id,
         'N' enreg_s,
         famcpta fam_lib,
         NULL fam_lib2,
         'T' tiers_typ
    FROM uv_cimputations i
    WHERE famcpta IS NOT NULL
      AND NOT EXISTS (SELECT 1 FROM se_CPT_FAM f WHERE f.fam_cod=i.famcpta)) ;


 UPDATE se_CPT_FAM tf SET enreg_id=(select enreg_id FROM &erp.CPT_FAM f WHERE f.fam_cod=tf.fam_cod) ;
 UPDATE se_CPT_FAM tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('fam_cod','CPT_FAM','O') ;

 commit;



select * from (
SELECT &outils.stragg(source) source,fam_cod, enreg_id, enreg_s, fam_lib, fam_lib2, tiers_typ
FROM (
  SELECT 'uv' source ,g.* FROM se_CPT_FAM g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.CPT_FAM g
  )
GROUP BY fam_cod, enreg_id, enreg_s, fam_lib, fam_lib2, tiers_typ
HAVING Count(1)!=2) r
ORDER BY fam_cod;